package com.indusfo.spc.untlis;

import com.indusfo.spc.exception.QueryFailedException;
import com.indusfo.spc.pojo.Classes;
import com.indusfo.spc.pojo.User;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.util.StringUtil;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.aspectj.util.FileUtil;
import org.springframework.core.io.Resource;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import java.io.*;
import java.lang.reflect.Array;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Time;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

/**
 * @author louk
 * @date 2019/10/17 22:38
 */
public class ExcelUtil {

    public  static String[][] uploadFile(MultipartFile multipartFile,int columns){
        String[][] array=null;
        //判断文件是否为空
        if(!multipartFile.isEmpty()){
            //获取到文件的后缀格式
            int begin = multipartFile.getOriginalFilename().indexOf(".");
            int length = multipartFile.getOriginalFilename().length();
            String typeName = multipartFile.getOriginalFilename().substring(begin, length);
            //判断文件是否是xlsx格式的
            if(".xlsx".equals(typeName)){

                try {
                    File file = multipartFileToFile(multipartFile);
                    FileInputStream fileInputStream=new FileInputStream(file);
                    XSSFWorkbook sheets = new XSSFWorkbook(fileInputStream);
                    XSSFSheet sheetAt = sheets.getSheetAt(0);
                    //获取总行数
                    int lastRowNum = sheetAt.getLastRowNum();
                    //定义一个变量
                    int count=0;
                    String content="";
                    array=new String[lastRowNum][4];
                    for (int i = 0; i < lastRowNum; i++) {
                        count=i+1;
                        XSSFRow row = sheetAt.getRow(count);
                        for (int y = 0; y < 4; y++) {
                            row.getCell(y).setCellType(CellType.STRING);
                            content=row.getCell(y).getStringCellValue();
                            if(content==null){
                                throw new QueryFailedException("第"+i+"行第"+y+"列的数据为空,请填写");
                            }
                            array[i][y]= row.getCell(y).getStringCellValue();
                        }
                    }

                    //对文件中的数据进行添加
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }else{
                throw new QueryFailedException("文件格式错误");
            }

        }else{
            throw new QueryFailedException("文件为空");
        }
            return array;
    }

    public static void inputStreamToFile(InputStream ins, File file) {
        try {

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static File multipartFileToFile(MultipartFile multipartFile){
        File file = null;
        InputStream ins = null;
        try {
            ins = multipartFile.getInputStream();
            file=new File(multipartFile.getOriginalFilename());
            OutputStream os = new FileOutputStream(file);
            int bytesRead = 0;
            byte[] buffer = new byte[8192];
            while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
                os.write(buffer, 0, bytesRead);
            }
            os.close();
            ins.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return file;

    }

    //判断行为空
    private int CheckRowNull(HSSFRow hssfRow){
        int num = 0;
        Iterator<Cell> cellItr =hssfRow.iterator();
        while(cellItr.hasNext()){
            Cell c =cellItr.next();
            if(c.getCellType() == HSSFCell.CELL_TYPE_BLANK){
                num++;
            }
        }
        return num;
    }

    public static void main(String[] args) throws IOException {

     /* File file=new File("C:\\Users\\DELL\\Desktop\\15.xlsx");
        FileInputStream fileInputStream=new FileInputStream(file);
        XSSFWorkbook sheets = new XSSFWorkbook(fileInputStream);
        XSSFSheet sheetAt = sheets.getSheetAt(0);
        int lastRowNum = sheetAt.getLastRowNum();
       *//* System.out.println(lastRowNum);
        int physicalNumberOfRows = sheetAt.getPhysicalNumberOfRows();
        System.out.println(physicalNumberOfRows);
        for (int i = 0; sheetAt.getRow(i)!=null; i++) {
            System.out.println(i);
        }*//*
        for (int i = 0; i < lastRowNum; i++) {
           int count=i+1;
            XSSFRow row = sheetAt.getRow(count);
            for (int y = 0; y < 4; y++) {
                row.getCell(y).setCellType(CellType.STRING);
                String content=row.getCell(y).getStringCellValue();
                if(content==null){
                    throw new QueryFailedException("第"+i+"行第"+y+"列的数据为空,请填写");
                }
                System.out.println(content);
//                array[i][y]= row.getCell(y).getStringCellValue();
            }
        }*/


    }


    public static String getCellValue(Sheet sheet, int r, int c, boolean date) {

        String data = "";
        try {
            Cell cell = sheet.getRow(r).getCell(c);
            if(cell==null){
                return null;
            }
            cell.setCellType(CellType.STRING);
            data = cell.getStringCellValue();
            data=StringUtils.trimToEmpty(data).replaceAll("\\s*", "");
        } catch (Exception e) {
            e.printStackTrace();
            data = null;
        }
        return data;

    }
    public static Date getDateCellValue(Sheet sheet, int r, int c, boolean date) {

        Date data ;
        try {
            Cell cell = sheet.getRow(r).getCell(c);
            if(cell==null || "".equals(cell.toString())){
                return null;
            }
            data = cell.getDateCellValue();
        } catch (Exception e) {
            e.printStackTrace();
            data = null;
        }
        return data;

    }






}
